IF EXISTS (
	SELECT SPECIFIC_NAME 
	FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE SPECIFIC_SCHEMA = N'testing'
		AND SPECIFIC_NAME = N'prc_DeleteQuestionGroup' 
)
	BEGIN
		DROP PROCEDURE testing.prc_DeleteQuestionGroup
		PRINT 'Dropping testing.prc_DeleteQuestionGroup'
	END
GO

/*-------------------------------------------------------------------------------------
** Name:	prc_DeleteQuestionGroup
** Purpose:	delete a question group
** 
** ------------------------------------------------------------------------------------
** Input Parameters: 
** @QuestionGroupId
** 
** ------------------------------------------------------------------------------------
** Usage
** EXEC testing.prc_DeleteQuestionGroup '1'
** EXEC testing.prc_DeleteQuestionGroup '1,2'
**
** ------------------------------------------------------------------------------------
** Author:    Hau
** Date:      2012-07-12
** Database:  SIVN_OnlineTestingEngine
**
** ------------------------------------------------------------------------------------
** Modifications:
** 2012-07-12	Hau		Create
** ----------------------------------------------------------------------------------*/

CREATE PROCEDURE [testing].[prc_DeleteQuestionGroup]
@QuestionGroupId varchar(100)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @tblID table
	(
		Id int
	)
	
	DECLARE @Id varchar(10)
	DECLARE @qgId int
	DECLARE @intPosition int

	SET @QuestionGroupId = LTRIM(RTRIM(@QuestionGroupId)) + ','
	SET @intPosition = CHARINDEX(',', @QuestionGroupId, 1)

	IF REPLACE(@QuestionGroupId, ',', '') <> ''
	BEGIN
		WHILE @intPosition > 0
		BEGIN
			SET @Id = LTRIM(RTRIM(LEFT(@QuestionGroupId, @intPosition - 1)))
			IF @Id <> ''
			BEGIN
				INSERT INTO @tblID(Id) VALUES (CAST(@Id as int))
			END
			SET @QuestionGroupId = RIGHT(@QuestionGroupId, LEN(@QuestionGroupId) - @intPosition)
			SET @intPosition = CHARINDEX(',', @QuestionGroupId, 1)
		END
	END
	
	DECLARE qgId_cursor CURSOR FOR  
	SELECT Id from @tblID

	OPEN qgId_cursor  
	FETCH NEXT FROM qgId_cursor INTO @qgId

	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		--delete question group detail
		delete from testing.QuestionGroupDetail where QuestionGroupId = @qgId
		
		--delete Comment
		delete from testing.Comment where QuestionGroupId = @qgId
		
		--delete Like
		delete from testing.[Like] where QuestionGroupId = @qgId
		
		--delete question group
		delete from testing.QuestionGroup where Id = @qgId
		   
		FETCH NEXT FROM qgId_cursor INTO @qgId  
	END  

	CLOSE db_cursor  
	DEALLOCATE db_cursor 
END

PRINT 'Created [testing].[prc_GetUserQuestionGroups]'
GO